<?php
require_once 'Zend/Db/Table/Abstract.php';

class Advertisement extends Zend_Db_Table_Abstract {
	
	const ID = "id";
	const USER = "user_id";
	const TYPE = "type";
	const FROM_CITY = "from_city";
	const TO_CITY = "to_city";
	const DEPARTURE_TIME = "departure_time";
	const ARRIVAL_TIME = "arrival_time";
	const TITLE = "title";
	const DESCRIPTION = "description";
	const WEIGHT = "weight";
	const PASSWORD = "password";
	const STATUS = "status";
	const CREATED = "created";
	const MODIFIED = "modified";
	
	const WANT = 'want';
	const LEASE = 'lease';
	
	protected $_name = 'advertisement';
	protected $_primary = 'id';
	
	protected $_dependentTables = array(
		'AdvertisementResponse',
	    'AdvertisementExtension',
	    'AdvertisementNotification'
	);
	
	protected $_referenceMap = array (
	'User' => array (
		'columns' => 'user_id', 
		'refTableClass' => 'User', 
		'refColumns' => 'id' 
	),
	'FromCity' => array (
		'columns' => 'from_city',
		'refTableClass' => 'City',
		'refColumns' => 'id'
	),
	'ToCity' => array (
		'columns' => 'to_city',
		'refTableClass' => 'City',
		'refColumns' => 'id'
	),
	'Category' => array(
		'columns' => 'category_id',
		'refTableClass' => 'Category',
		'refColumns' => 'id'
	)
	);
	
	/**
	 * Add or update advertisement.
	 * if id is include in the $data, then update
	 * otherwise, add new entry.
	 * 
	 * @param $data
	 */
	public function addOrUpdateAdvertisement($data) {
		if (isset($data['id'])) {
			$adv = $this->findById($data['id']);
			if (isset($data['description'])) {
				$adv->description = $data['description'];
			}
			if (isset($data['type'])) {
				$adv->type = $data['type'];
			}
			$adv->user_id = $data['user_id'];
			if (isset($data['weight'])) {
				$adv->weight = $data['weight'];
			}
			if (isset($data[Advertisement::FROM_CITY])) {
				$adv->from_city = $data[Advertisement::FROM_CITY];
			}
			if (isset($data[Advertisement::TO_CITY])) {
				$adv->to_city = $data[Advertisement::TO_CITY];
			}
			if (isset($data[Advertisement::DEPARTURE_TIME])) {
				$adv->departure_time = $data[Advertisement::DEPARTURE_TIME];
			}
			if (isset($data[Advertisement::ARRIVAL_TIME])) {
				$adv->arrival_time = $data[Advertisement::ARRIVAL_TIME];
			}
			$adv->modified = $data['modified'];
			$adv->save();
		} else {
			$adv = $this->createRow($data);
			$id = $adv->save();
			$adv = $this->findById($id);
		}
		return $adv;
	}
	
	/**
	 * Find advertisement by id.
	 * 
	 * @param $id
	 * @return return Advertisement
	 */
	public function findById($id) {
		return $this->find($id)->current();
	}
	
	/**
	 * Get user.
	 * 
	 * @param id advertisement id
	 */	
	public static function getUser($id) {
		$obj = new Advertisement();
		$advertisement = $obj->findById($id);
		return $advertisement->findParentRow ('User');
	}
	
	/**
	 * Get from city.
	 * 
	 * @param id advertisement id
	 */	
	public static function getFromCity($id) {
		$obj = new Advertisement();
		$advertisement = $obj->findById($id);
		return $advertisement->findParentRow ('City', 'FromCity');
	}
	
	/**
	 * Get to city.
	 * 
	 * @param id advertisement id
	 */	
	public static function getToCity($id) {
		$obj = new Advertisement();
		$advertisement = $obj->findById($id);
		return $advertisement->findParentRow ('City', 'ToCity');
	}
	
	/**
	 * Get category.
	 * 
	 * @param $advertisement
	 * @return return advertisement's category
	 */
	public static function getCategory($advertisement) {
		return $advertisement->findParentRow('Category');
	}
	
	/**
	 * Find advertis by password, password is unique in the table.
	 * 
	 * @param $password
	 * @return return Advertisement object
	 */
	public function findAdvertisementByPassword($password) {
		$select = $this->select()->where('password=?', $password);
		return $this->fetchRow($select);
	}
	
	/**
	 * Find all active and closed advertisements.
	 */
	public function findAll() {
		$where = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
        ->setIntegrityCheck(false)
        ->from(array("adv"=>"advertisement"), array("advertismentId"=>"adv.id", "user_id"=>"adv.user_id", "type"=>"adv.type", "from_city"=>"adv.from_city", "to_city"=>"adv.to_city", "departure_time"=>"adv.departure_time", "arrival_time"=>"adv.arrival_time", "description"=>"adv.description", "weight"=>"adv.weight", "created"=>"adv.created", "status"=>"adv.status"))
        ->join(array("user"=>"user"), "user.id=adv.user_id", array("username"=>"user.name", "small_image"=>"user.small_image"))
        ->where("adv.status='".PostStatus::CLOSED."' OR adv.status = '".PostStatus::ACTIVE."'")
        ->group("adv.id")
        ->order("adv.created DESC");
        return $this->fetchAll($where);
	}
	
	/**
	 * Find advertisement by country, include active and closed post.
	 * 
	 * @param $country
	 * @return return array of Advertisement
	 */
	public function findByCountry($country) {
		$where = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
        ->setIntegrityCheck(false)
        ->from(array("adv"=>"advertisement"), array("advertismentId"=>"adv.id", "user_id"=>"adv.user_id", "type"=>"adv.type", "from_city"=>"adv.from_city", "to_city"=>"adv.to_city", "departure_time"=>"adv.departure_time", "arrival_time"=>"adv.arrival_time", "description"=>"adv.description", "weight"=>"adv.weight", "created"=>"adv.created", "status"=>"adv.status"))
        ->join(array("city"=>"city"), "city.id=adv.from_city OR city.id=adv.to_city")
        ->join(array("country"=>"country"), "city.country_id = country.id")
        ->where("adv.status='".PostStatus::CLOSED."' OR adv.status = '".PostStatus::ACTIVE."'")
        ->where("country.id=?", $country->id)
        ->group("adv.id")
        ->order("adv.created DESC");
        
//		$select = $this->select()->where('status = "'.PostStatus::CLOSED.'" OR status = "'.PostStatus::ACTIVE.'"')
//			->where('city_id=?', $city->id)
//			->order('created desc');
		return $this->fetchAll($where);
	}
	
	/**
	 * Close advertisement.
	 * @param $id
	 */
	public function closeAdvertisement($id) {
		$advertisement = $this->findById($id);
		$advertisement->status = PostStatus::CLOSED;
		$advertisement->save();
	}
	
	/**
	 * Delete advertisement.
	 */
	public function deleteAdvertisement($id) {
		$advertisement = $this->findById($id);
		$advertisement->status = PostStatus::DELETED;
		$advertisement->save();
	}
	
	/**
	 * Search by criteria data.
	 * 
	 * @param $data
	 * @return return search result
	 */
	public function searchByCriteria($data) {
		$from_country = $data[Constant::VAR_SEARCH_DATA_FROM_COUNTRY];
		$to_country = $data[Constant::VAR_SEARCH_DATA_TO_COUNTRY];
		$from_city = $data[Constant::VAR_SEARCH_DATA_FROM_CITY];
		$to_city = $data[Constant::VAR_SEARCH_DATA_TO_CITY];
		$latest_arrival_date = $data[Constant::VAR_SEARCH_DATA_LASTEST_ARRIVAL_DATE];
		$type = $data[Constant::VAR_SEARCH_DATA_TYPE];
		
		$where = $this->select(Zend_Db_Table::SELECT_WITH_FROM_PART)
        ->setIntegrityCheck(false)
        ->from(array("adv"=>"advertisement"), array("advertismentId"=>"adv.id", "user_id"=>"adv.user_id", "type"=>"adv.type", "from_city"=>"adv.from_city", "to_city"=>"adv.to_city", "departure_time"=>"adv.departure_time", "arrival_time"=>"adv.arrival_time", "description"=>"adv.description", "weight"=>"adv.weight", "created"=>"adv.created", "status"=>"adv.status"))
        ->join(array("user"=>"user"), "user.id=adv.user_id", array("username"=>"user.name", "small_image"=>"user.small_image"));
		if ($from_country != 0) {
        	$where = $where->join(array("city"=>"city"), "city.id=adv.from_city ");
        	$where = $where->join(array("country"=>"country"), "city.country_id = country.id");
        }
		if ($to_country != 0) {
        	$where = $where->join(array("to_city"=>"city"), "to_city.id=adv.to_city ");
        	$where = $where->join(array("to_country"=>"country"), "to_city.country_id = to_country.id");
        }
//        ->join(array("city"=>"city"), "city.id=adv.from_city OR city.id=adv.to_city")
        
        $where = $where->where("adv.status='".PostStatus::CLOSED."' OR adv.status = '".PostStatus::ACTIVE."'");
        if ($type!='all') {
        	$where = $where->where("adv.type=?", $type);
        }
        if ($from_city != 0) {
        	$where = $where->where("adv.from_city=?", $from_city);
        } else if ($from_country != 0) {
        	$where = $where->where("country.id=?", $from_country);
        }
        if ($to_city != 0) {
        	$where = $where->where("adv.to_city=?", $to_city);
        } else if ($to_country != 0) {
        	$where = $where->where("to_country.id=?", $to_country);
        }
        if (!empty($latest_arrival_date)) {
        	$where = $where->where("adv.arrival_time<=?", $latest_arrival_date);
        }
        $where = $where->group("adv.id")
        ->order("adv.created DESC");
        
        return $this->fetchAll($where);
		/*
		$select = $this->select();
		if ($data[Constant::VAR_SEARCH_DATA_TYPE] != 'all') {
			$select = $select->where('type=?',$data['type']);
		}
		if (!empty($from_city) && $from_city != 0) {
			$select = $select->where("from_city=?", $from_city);
		}
		if (!empty($to_city) && $to_city != 0) {
			$select = $select->where("to_city=?", $to_city);
		}
		if (!empty($latest_arrival_date)) {
			$select = $select->where("arrival_time<=?", $latest_arrival_date);
		}
		return $this->fetchAll($select);
		*/
		
	}
	
	/**
	 * Find all advertisments which need to send notification.
	 * 
	 * @param $data
	 */
	public function findAdvertisementToSendNofication($date) {
		$where = $this->select()->where('modified <= ?', $date)
		->where('status = ?', PostStatus::ACTIVE);
		return $this->fetchAll($where);
	}
	
	/**
	 * Find all advertisements to be closed, if stop_date is older than current date, then marked it as closed.
	 */
	public function findAdvertisementToClose() {
		$where = $this->select()
		->where("DATE(arrival_time)<=?", date("Y-m-d"))
		->where("status=?", PostStatus::ACTIVE);
		return $this->fetchAll($where);
	}
}
?>